#!coding:utf8
'''
Created on Aug 7, 2012

@author: join
'''

from lottery_jx.common import tools,utils, Constant
from lottery_jx.models import Account, Trade,TicketConstant,TicketLoan,\
    Statistics, SystemSettings,SevenColor,ThreeD,FiveBall,SixPlusOne

#import json
import logging
import datetime
from django.http import HttpResponse
import xlwt
from django.db.models.query_utils import Q
from decimal import Decimal, getcontext
LOG = logging.getLogger('console')

#from account import type_dict,type_dict1
method_dict={0:u'刷银联卡',1:u'投币',2:u'代购'}
type_dict = {Constant.SHOP_ACCOUNT:u'店主', Constant.AGENT_ACCOUNT:u'代理商', Constant.TIANFU_ACCOUNT:u'添福公司'}
type_dict1 = {Constant.TRADE_ACTION:u'交易中', Constant.TRADE_FAILED:u'交易失败', Constant.TRADE_SUCCESSED:u'交易成功', Constant.TRADE_CENCELED:u'交易取消',Constant.TRADE_PRINTER_BROKEN:u'打印失败'}
type_dict2 = {Constant.RECHARGE:u'充值', Constant.BUY_LOTTERY:u'购买彩票', Constant.TO_CASH:u'提现', Constant.BACK_AWARD:'返奖'}
type_dict3 = {Constant.TicketConstant:u'时时彩', Constant.TicketLoan:u'双色球', Constant.SevenColor:u'七乐彩',Constant.ThreeD:u'福彩3D',Constant.FiveBall:u'十五选五',Constant.SixPlusOne:u'东方6+1' }
type_dict4 = {Constant.SingleWay:u'单式', Constant.DoubleWay:u'复式', Constant.BaoHao:u'包号', Constant.HeZhi:u'和值', Constant.DanTuo:u'胆托'}
type_dict5 = {Constant.COMISSION_ACTION:u'待审核', Constant.COMISSION_SUCCESS:u'审核通过',Constant.COMISSION_CASH:u'提成处理完成'}
type_dict6 = {Constant.TRADE_ACTION:u'交易中', Constant.TRADE_FAILED:u'交易失败', Constant.TRADE_SUCCESSED:u'交易成功', Constant.TRADE_CENCELED:'交易取消', Constant.TRADE_SUCC:u'已兑奖'}
ticket_model_ticket = {1:TicketLoan,0:TicketConstant,2:SevenColor,3:ThreeD,4:FiveBall,5:SixPlusOne}

@tools.render
def saleinquire(request):
    '''  销售信息查询 '''
    return 'count_inquire/saleinquire.html',{'datagrid_id':'saleinquire'}


@tools.render
def statistics(request):
    '''当月销售提成统计'''
    return 'count_inquire/statistics.html',{'datagrid_id':'comiss'}


@tools.render
def balance(request):
    '''当月销售提成统计'''
    return 'count_inquire/balance.html'


def total_inout(account,start,end):
    getcontext().prec=10
    chongzhi = Decimal(0)
    cz_bank = Decimal(0) #刷卡充值
    cz_rg = Decimal(0) #人工充值
    xiaoshou = Decimal(0) #投币和代购
    xs_bank = Decimal(0) #刷银行卡
    ticheng = Decimal(0)
    tixian = Decimal(0)
    prize = Decimal(0)
    
    if start:
        trades = Trade.objects.filter(Q(accountid=account) | Q(prizeaccount=account)).filter(deleted=False).\
            filter(Q(updated_at__range=[start,end])|Q(created_at__range=[start,end])).exclude(status=0).exclude(status=1)
    else:
        trades = Trade.objects.filter(Q(accountid=account) | Q(prizeaccount=account)).filter(deleted=False).\
            exclude(status=0).exclude(status=1)
    ratio = account.commissionRatio if account.commissionRatio else 0.07
    
    for trade in trades:
        if trade.prizeaccount==account:
            if end and trade.updated_at>utils.format_datetime(end):
                continue
            _model = ticket_model_ticket.get(trade.tradeTicketType)
            tickets = _model.objects.filter(deleted=False,tradeId=trade)
            money = sum([t.prizemoney if t.prizemoney else 0 for t in tickets])
            prize += Decimal('%s'%money)
        if trade.tradeType==Constant.TRADE_BUY_LOTTERY:
            if (start and trade.created_at<utils.format_datetime(start)) or trade.accountid!=account:
                continue
            if trade.tradeMethod == Constant.TRADEMETHOD_CARD:
#                in_total += ratio*trade.tradeAmount
                xs_bank += Decimal('%s'%trade.tradeAmount)
                ticheng += Decimal('%s'%ratio)*Decimal('%s'%trade.tradeAmount)
            else:
#                in_total += ratio*trade.tradeAmount
                ticheng += Decimal('%s'%ratio)*Decimal('%s'%trade.tradeAmount)
                xiaoshou += Decimal('%s'%trade.tradeAmount)
        elif trade.tradeType==Constant.TRADE_RECHARGE:
#            in_total += ratio*trade.tradeAmount
            if trade.tradeMethod == Constant.TRADEMETHOD_CARD:
                cz_bank += Decimal('%s'%trade.tradeAmount)
            else:
                cz_rg += Decimal('%s'%trade.tradeAmount)
            chongzhi += Decimal('%s'%trade.tradeAmount)
        elif trade.tradeType==Constant.TRADE_WITHDRAW:
            tixian += Decimal('%s'%trade.tradeAmount)
            
    in_total = chongzhi+ticheng+prize
    out_total = xiaoshou+tixian
    return {"chongzhi_total":float(chongzhi),'xiaoshou_total':float(xiaoshou+xs_bank),'ticheng_total':float(ticheng),'tixian_total':float(tixian),\
            'prize_total':float(prize),'in_total':float(in_total),'out_total':float(out_total),'xs_bank':float(xs_bank),'xiaoshou':float(xiaoshou),\
            'cz_bank':float(cz_bank),'cz_rg':float(cz_rg)}


@tools.ajaxrender
def balance_detail(request):
    try:
        page = int(request.POST.get('page', 1))
        rows = int(request.POST.get('rows', 20))
        account = request.POST.get('account',None)
        start = request.POST.get('start',None)
        end = request.POST.get('end',None)
        
        chongzhi = Decimal(0)
        xiaoshou = Decimal(0)
        xs_bank = Decimal(0)
        ticheng = Decimal(0)
        tixian = Decimal(0)
        prize = Decimal(0)
        
        if account:
            accounts = Account.objects.filter(account=account)
            account_s = accounts[0] if accounts else None
        
        if start:
            trades = Trade.objects.filter(Q(accountid=account_s) | Q(prizeaccount=account_s)).\
                filter(deleted=False).filter(Q(updated_at__range=[start,end])|Q(created_at__range=[start,end])).exclude(status=0).exclude(status=1).order_by('-updated_at')[(page-1)*rows: page*rows]
            totalcount = Trade.objects.filter(Q(accountid=account_s) | Q(prizeaccount=account_s)).\
                filter(deleted=False).filter(Q(updated_at__range=[start,end])|Q(created_at__range=[start,end])).exclude(status=0).exclude(status=1).count()
        else:
            trades = Trade.objects.filter(Q(deleted=False,accountid=account_s) | Q(deleted=False,prizeaccount=account_s)).\
                exclude(status=0).exclude(status=1).order_by('-updated_at')[(page-1)*rows: page*rows]
            totalcount = Trade.objects.filter(Q(deleted=False,accountid=account_s) | Q(deleted=False,prizeaccount=account_s)).\
                exclude(status=0).exclude(status=1).count()
        
        data_list = []
        status_dict = {'0':u'交易中','1':u'交易失败','2':u'交易成功','3':u'交易取消','4':u'打印失败','5':u'None'}
        method_dict = {0:u'刷银联卡',1:u'投币',2:u'代购'}
        
        for trade in trades:
            account = trade.accountid
            ratio = account.commissionRatio if account.commissionRatio else 0.07
            
            if trade.prizeaccount==account_s:
                if end and trade.updated_at>utils.format_datetime(end):
                    continue
                _model = ticket_model_ticket.get(trade.tradeTicketType)
                tickets = _model.objects.filter(deleted=False,tradeId=trade)
                money = sum([t.prizemoney if t.prizemoney else 0 for t in tickets])
                prize += Decimal('%s'%money)
                data_list.append({'account_num':'%s%s'%(account.account,account.address),'item':'%s%s'%(u'返奖',\
                            utils.convert_datetime_to_str(trade.updated_at, '%Y-%m-%d %H:%M:%S')),'amount':trade.tradeAmount,\
                          'inmoney':money,'outmoney':'','status':status_dict.get(str(trade.status),'5'),\
                          'tradetime':utils.convert_datetime_to_str(trade.created_at, '%Y-%m-%d %H:%M:%S'),\
                          'trademethod':method_dict.get(trade.tradeMethod),'tickettype':type_dict3.get(trade.tradeTicketType)})
            
            if trade.tradeType==Constant.TRADE_BUY_LOTTERY:
                if (start and trade.created_at<utils.format_datetime(start)) or trade.accountid!=account_s:
                    continue
                if trade.tradeMethod == Constant.TRADEMETHOD_CARD:
                    xs_bank += Decimal('%s'%trade.tradeAmount)
                    ticheng += Decimal('%s'%ratio)*Decimal('%s'%trade.tradeAmount)
                    data_list.append({'account_num':account.account,'item':u'购买彩票','amount':trade.tradeAmount,\
                                      'inmoney':'%.2f'%(ratio*trade.tradeAmount),'outmoney':'','status':status_dict.get(str(trade.status),'5'),\
                                      'tradetime':utils.convert_datetime_to_str(trade.created_at, '%Y-%m-%d %H:%M:%S'),\
                                      'trademethod':method_dict.get(trade.tradeMethod),'tickettype':type_dict3.get(trade.tradeTicketType)})
                else:
                    ticheng += Decimal('%s'%ratio)*Decimal('%s'%trade.tradeAmount)
                    xiaoshou += Decimal('%s'%trade.tradeAmount)
                    #支出购买彩票的成本
                    data_list.append({'account_num':account.account,'item':u'购买彩票','amount':trade.tradeAmount,\
                                      'inmoney':'','outmoney':trade.tradeAmount,'status':status_dict.get(str(trade.status),'5'),\
                                      'tradetime':utils.convert_datetime_to_str(trade.created_at, '%Y-%m-%d %H:%M:%S'),\
                                      'trademethod':method_dict.get(trade.tradeMethod),'tickettype':type_dict3.get(trade.tradeTicketType)})
                    #收入购买彩票的提成
                    data_list.append({'account_num':account.account,'item':u'购买彩票','amount':trade.tradeAmount,\
                                      'inmoney':'%.2f'%(ratio*trade.tradeAmount),'outmoney':'','status':status_dict.get(str(trade.status),'5'),\
                                      'tradetime':utils.convert_datetime_to_str(trade.created_at, '%Y-%m-%d %H:%M:%S'),\
                                      'trademethod':method_dict.get(trade.tradeMethod),'tickettype':type_dict3.get(trade.tradeTicketType)})
            
            elif trade.tradeType==Constant.TRADE_RECHARGE:
                chongzhi += Decimal('%s'%trade.tradeAmount)
                data_list.append({'account_num':account.account,'item':u'充值','amount':trade.tradeAmount,\
                                      'inmoney':trade.tradeAmount,'outmoney':'','status':status_dict.get(str(trade.status),'5'),\
                                      'tradetime':utils.convert_datetime_to_str(trade.created_at, '%Y-%m-%d %H:%M:%S'),\
                                      'trademethod':'N/A','tickettype':'N/A'})
            elif trade.tradeType==Constant.TRADE_WITHDRAW:
                tixian += Decimal('%s'%trade.tradeAmount)
                data_list.append({'account_num':account.account,'item':u'提现','amount':trade.tradeAmount,\
                                      'inmoney':'','outmoney':trade.tradeAmount,'status':status_dict.get(str(trade.status),'5'),\
                                      'tradetime':utils.convert_datetime_to_str(trade.created_at, '%Y-%m-%d %H:%M:%S'),\
                                      'trademethod':'N/A','tickettype':'N/A'})
        
        total_in = chongzhi+ticheng+prize
        total_out = xiaoshou+tixian
        tt = total_inout(account_s,start,end)
        data_list.insert(0,{'account_num':u'<b><font color="red">页收入:%s</font></b>'%float(total_in),'item':u'<b><font color="red">页支出:%s</font></b>'%float(total_out),\
                            'amount':u'<b>销售:%s</b>'%float(xiaoshou+xs_bank),\
                                      'inmoney':u'<b>提成:%.2f</b>'%float(ticheng),'outmoney':'<b>充值：%s</b>'%float(chongzhi),\
                                      'trademethod':u'<b>当前额度：%s</b>'%account_s.balance,'tradetime':u'<b>返奖:%.2f</b>'%float(prize),\
                                      'status':'<b>提现:%s</b>'%float(tixian),'tickettype':'N/A'})
        
        data_list.insert(0,{'account_num':u'<b><font color="red">总收入:%s</font></b>'%tt['in_total'],'item':u'<b><font color="red">总支出:%s</font></b>'%tt['out_total'],\
                            'amount':u'<b>销售:%s</b>'%float(xiaoshou),\
                                      'inmoney':u'<b>提成:%.2f</b>'%float(ticheng),'outmoney':'<b>充值：%s</b>'%float(chongzhi),\
                                      'trademethod':u'<b>当前额度：%s</b>'%account_s.balance,'tradetime':u'<b>返奖:%.2f</b>'%float(prize),\
                                      'status':'<b>提现:%s</b>'%float(tixian),'tickettype':'N/A'})
        
        
        return {'rows':data_list,'total':totalcount} #,'total_in':total_in,'total_out':total_out,'balance':balance}
    except Exception as e:
        print e
        LOG.exception("error in balance_detail:%s" % e)
        return None  

@tools.ajaxrender
def statitsticsDatalist(request):
    '''统计数据展示'''
    try:
        data_list = []
        start = request.POST.get('start',None)
        end = request.POST.get('end',None)
        page = int(request.POST.get('page', 1))
        rows = int(request.POST.get('rows', 20))
        if start and end:
            start = start + " 00:00:00"
            end = end + " 23:59:59"
        else:
            return None
        
        accounts = Account.objects.filter(deleted=False)[(page-1)*rows: page*rows]
        count = Account.objects.filter(deleted=False).count()
        for account in accounts:
            if account.accountType == Constant.TIANFU_ACCOUNT:
                continue
            data = total_inout(account,start,end)
            data_list.append({'accountname':account.account,'accounttype': type_dict.get(account.accountType),
                'balance':account.balance,'totalamount': data['xiaoshou_total'],'xs_bank':data['xs_bank'],
                'xs_toubi':data['xiaoshou'],'chongzhi':data['chongzhi_total'],'prize':data['prize_total'],
                'ticheng':data['ticheng_total'],'tixian':data['tixian_total'],'cz_bank':data['cz_bank'],'cz_rg':data['cz_rg']
            })
        
        values = {'total':count,'rows': data_list}
        return values
    except Exception as e:
        LOG.exception("统计数据失败，异常信息为：%s" % e)
        return None  
    

def do_statistics(year,month):
    '''统计数据查询'''
    try:  
        LOG.info("统计数据开始，月份：%s-%s" %(year,month))
        if year and month:
            cur_year = year
            cur_month = month +1
        else:
            cur_year = int(str(datetime.date.today())[0:4])
            cur_month = int(str(datetime.date.today())[5:7])
            
        statistics_month = '%s-%s'%(cur_year,cur_month-1) #统计月份   
        start_time = datetime.datetime(cur_year,cur_month-1,1)
        end_time = datetime.datetime(cur_year,cur_month,1)  
        accounts = Account.objects.filter(deleted=False)
        banksetting = SystemSettings.objects.get(itemname='bank_ratio')   
        settingvalue = banksetting.itemvalue
        if banksetting.itemvalue2:
            settingvalue = banksetting.itemvalue2 if datetime.datetime.now()>banksetting.legaldate else banksetting.itemvalue
        bank_ratio = float(settingvalue)  #银联刷卡手续费利率  
        #统计每个账户的销售提成数据
        agents_accounts = []
        company_accounts = []
        agent_card_sales = {}
        agent_cash_sales = {}
        agent_cash_sales1 = {}
        company_cash_sales = 0.0
        company_cash_sales1 = 0.0
        company_card_sales = 0.0   
        for account in accounts:
            if account.accountType == Constant.AGENT_ACCOUNT:
                agents_accounts.append(account)
                continue       
            elif account.accountType == Constant.TIANFU_ACCOUNT:
                company_accounts.append(account)
                continue       
            trades = Trade.objects.filter(deleted=False,tradeType=Constant.TRADE_BUY_LOTTERY,status=Constant.TRADE_SUCCESSED,
                                          updated_at__gte=start_time,updated_at__lte=end_time,accountid=account)
#            for trade in trades:
#                print Constant.TRADEMETHOD_CASH
#                if trade.tradeMethod == Constant.TRADEMETHOD_CASH:
#                    print '**********',trade.tradeAmount
            amount_cash = sum([trade.tradeAmount for trade in trades if trade.tradeMethod==Constant.TRADEMETHOD_CASH])
            amount_cash = 0 if not amount_cash else amount_cash
            amount_cash1 = sum([trade.tradeAmount for trade in trades if trade.tradeMethod==Constant.TRADEMETHOD_SHOPKEEPER])
            amount_cash1 = 0 if not amount_cash1 else amount_cash1
            amount_card = sum([trade.tradeAmount for trade in trades if trade.tradeMethod==Constant.TRADEMETHOD_CARD])
            amount_card = 0 if not amount_card else amount_card
            company_cash_sales += amount_cash
            company_cash_sales1 += amount_cash1
            company_card_sales += amount_card
            if agent_card_sales.has_key(account.parentid):
                print agent_card_sales[account.parentid]
                agent_card_sales[account.parentid] += amount_card
                agent_cash_sales[account.parentid] += amount_cash
                agent_cash_sales1[account.parentid] += amount_cash1
            else:
                agent_card_sales[account.parentid] = amount_card
                agent_cash_sales[account.parentid] = amount_cash 
                agent_cash_sales1[account.parentid] = amount_cash1
                
            bank_commision = amount_card*bank_ratio #刷卡手续费
            ratio = 0.0 if not account.commissionRatio else account.commissionRatio
            comission = (amount_cash+amount_card+amount_cash1)*ratio
            if not account.commissionRatio or account.commissionRatio == 'None':
                commissionRatio = 0.0
            else:
                commissionRatio = account.commissionRatio
            Statistics.objects.create(account=account,bankRatio=bank_ratio,commissionRatio=commissionRatio,
                    salesAmount=amount_cash+amount_card+amount_cash1,salesCard=amount_card,salesCash=amount_cash,shopkeeper=amount_cash1,status=0,
                    commission=comission,statistics_month=statistics_month,bankcomission=bank_commision) #保存店主统计数据到统计表中    
        #统计代理的销售提成数据
        for agent_account in agents_accounts:
            agent_amount_cash = agent_cash_sales.get(agent_account.id,0.0)
            agent_amount_cash1 = agent_cash_sales1.get(agent_account.id,0.0)
            agent_amount_card = agent_card_sales.get(agent_account.id,0.0)
            agent_comission = (agent_amount_cash+agent_amount_cash1+agent_amount_card)*agent_account.commissionRatio
            agent_bank_commision = agent_amount_card*bank_ratio
        
            if not agent_account.commissionRatio or agent_account.commissionRatio == 'None':
                commissionRatio = 0.0
            else:
                commissionRatio = agent_account.commissionRatio
      
            Statistics.objects.create(account=agent_account,bankRatio=bank_ratio,commissionRatio=commissionRatio,
                    salesAmount=agent_amount_cash+agent_amount_card+agent_amount_cash1,salesCard=agent_amount_card,salesCash=agent_amount_cash,
                    shopkeeper=agent_amount_cash1,commission=agent_comission,statistics_month=statistics_month,bankcomission=agent_bank_commision,status=0)

        company_account = company_accounts[0]
        if not company_account.commissionRatio or company_account.commissionRatio == 'None':
            commissionRatio = 0.0
        else:
            commissionRatio = company_account.commissionRatio
        company_comission = (company_cash_sales+company_cash_sales1+company_card_sales)*commissionRatio
        company_bank_commision = company_card_sales*bank_ratio
        Statistics.objects.create(account=company_account,bankRatio=bank_ratio,commissionRatio=company_account.commissionRatio,
                    salesAmount=company_cash_sales+company_card_sales+company_cash_sales1,salesCard=company_card_sales,salesCash=company_cash_sales,
                    shopkeeper=company_cash_sales1,commission=company_comission,statistics_month=statistics_month,bankcomission=company_bank_commision,status=0)    
        return u'当月数据统计完成',200
    except Exception as e:
        print str(e)
        LOG.exception("统计数据查询失败，异常信息为：%s" % e)
        return u'系统异常，更新失败',500
    

@tools.render
def edit(request,id):
    '''显示提成审核页面'''
    try:
        if not id:
            return u'系统错误',500
        comission = Statistics.objects.get(id=id)    
        return 'count_inquire/comission.html',{'comi':comission}
    except Exception as e:
        print str(e)
        LOG.exception("页面显示失败，异常信息为：%s" % e)
        return u'系统异常，更新失败',500


@tools.ajaxrender
def submitEdit(request):
    '''提交提成审核'''
    try:
        id = request.POST.get('id')
        id = int(id)
        st=Statistics.objects.filter(deleted=False,id=id).all()   
        st=st[0]
        st.status=1 
        st.save()           
        return u'通过审核',200
    except Exception as e:
        print str(e)
        return u'审核失败。错误原因：%s' % str(e),500

@tools.ajaxrender
def re_edit(request):
    '''代理提成返还'''
    try:
        id = request.POST.get('id')
        id = int(id)
        st=Statistics.objects.filter(deleted=False,id=id).all()   
        st=st[0]
        st.status=2 
        st.save()           
        return u'通过审核',200
    except Exception as e:
        print str(e)
        LOG.exception("代理提成返还，异常信息为：%s" % e)
        return u'系统异常，更新失败',500



@tools.ajaxrender
def re_edit1(request):
    '''店主提成返还'''
    try:
        id = request.POST.get('id')
        id = int(id)
        st=Statistics.objects.filter(deleted=False,id=id).all() 
        st=st[0]
        ac=Account.objects.filter(deleted=False,id=st.account.id).all() 
        ac=ac[0] 
        ac.balance=ac.balance+st.commission
        st.status=2 
        st.save() 
        ac.save()          
        return u'通过审核',200
    except Exception as e:
        print str(e)
        LOG.exception("店主提成返还，异常信息为：%s" % e)
        return u'系统异常，更新失败',500

@tools.ajaxrender
def saleinfo(request):
    '''查询销售明细'''
    try:
        loginuser = request.session['user']
        userid=loginuser.id
        page = int(request.POST.get('page', 1))
        rows = int(request.POST.get('rows', 15))
        query_key = request.POST.get('query_key', None)
        query_value = request.POST.get('query_value', None)
        
        params = {'deleted': 0,'tradeType':Constant.TRADE_BUY_LOTTERY}
        if query_key and query_value:
            params.update({'accountid__%s__contains' % query_key: query_value})
            
        datalist=[]
        usertype=loginuser.accountType
        
        if usertype==Constant.TIANFU_ACCOUNT: #登录人是公司
            params.update({'accountid__deleted':0})
            trades = Trade.objects.filter(**params).order_by('-tradeTime')[(page-1)*rows: page*rows]
            amount = Trade.objects.filter(**params).count()
        elif usertype==Constant.AGENT_ACCOUNT: #如果是代理
            params.update({'accountid__parentid':userid,'accountid__deleted':0})
            trades = Trade.objects.filter(**params).order_by('-tradeTime')[(page-1)*rows: page*rows]
            amount = Trade.objects.filter(**params).count()
        elif usertype==Constant.SHOP_ACCOUNT: #如果是店主 
            params.update({'accountid':request.session['user']})
            trades = Trade.objects.filter(**params).order_by('-tradeTime')[(page-1)*rows: page*rows]
            amount = Trade.objects.filter(**params).count()
        
        if trades:
            for trade in trades:
                if trade.tradeTicketType is None:
                    trade.tradeTicketType="暂没种类"
                elif trade.tradeTicketType or not trade.tradeTicketType:
                    trade.tradeTicketType=type_dict3.get(trade.tradeTicketType)
                datalist.append({
                                'tradeid': trade.id,
                                'account_num': trade.accountid.account,
                                'name': trade.accountid.name,
                                'loginname': trade.accountid.loginname,
                                'ticktype':trade.tradeTicketType,
                                'period':trade.period.periodid,                                 
                                'tradamount':trade.tradeAmount,
                                'tradeMethod':method_dict.get(trade.tradeMethod),
                                'tradtime':utils.convert_datetime_to_str(trade.tradeTime, "%Y-%m-%d %H:%M:%S"),
                                'status':type_dict1.get(trade.status),
                                'desc':trade.desc
                             })
            result = {'total':amount,'rows': datalist}
            return result
            
    except Exception as e:
        print str(e)
        LOG.exception("查询失败，异常信息为：%s" % e)
        return u'系统异常，更新失败',500
            
    
@tools.render
def infodetail(request,tradeid):
    '''  先得到选择的交易的id ,根据交易的彩票类型到相应的彩票中查找相应的交易'''
    
    try:
        datalist=[]
        result={}
        trade=Trade.objects.filter(id=tradeid,deleted=0).all()
        tradetickettype=trade[0].tradeTicketType
        params = {'deleted': 0,'tradeId':trade[0]}
        tickets = None
        if tradetickettype==Constant.TicketConstant:
            tickets=TicketConstant.objects.filter(**params).order_by('-buytime').all()         
        elif tradetickettype==Constant.TicketLoan:
            tickets=TicketLoan.objects.filter(**params).order_by('-buytime').all()
        elif tradetickettype==Constant.SevenColor:
            tickets=SevenColor.objects.filter(**params).order_by('-buytime').all()
        elif tradetickettype==Constant.ThreeD:
            tickets=ThreeD.objects.filter(**params).order_by('-buytime').all()
        elif tradetickettype==Constant.FiveBall:
            tickets=FiveBall.objects.filter(**params).order_by('-buytime').all()
        elif tradetickettype==Constant.SixPlusOne:
            tickets=SixPlusOne.objects.filter(**params).order_by('-buytime').all()
            
        if tickets:
            for ticket in tickets:
                if not ticket.prizemoney or ticket.prizemoney=="None":
                    ticket.prizemoney=0.0
                    
                if not ticket.prizetime or ticket.prizetime=="None":
#                    ticket.prizetime=utils.format_datetime("1900-01-01", "%Y-%m-%d")
                    ticket.prizetime="暂未兑奖"
                elif ticket.prizetime:        
                    ticket.prizetime=utils.convert_datetime_to_str(ticket.prizetime, "%Y-%m-%d %H:%M:%S")
                datalist.append({
                                 'Number':ticket.lotteryNumber.split(':')[0],
                                 'way':type_dict4.get(int(ticket.lotteryNumber.split(':')[2])),
                                 'multiple':ticket.lotteryNumber.split(':')[3],
                                 'buytime':utils.convert_datetime_to_str(ticket.buytime, "%Y-%m-%d %H:%M:%S"),
                                 'validitytime':utils.convert_datetime_to_str(ticket.validitytime, "%Y-%m-%d %H:%M:%S"),
                                 'prizetime':ticket.prizetime,
                                 'status':type_dict6.get(ticket.status),
                                 'buymoney':ticket.buymoney,
                                 'prizemoney':ticket.prizemoney,
                                 'desc':ticket.desc
                              })
            result['tickets']=datalist
        return 'count_inquire/infodetail.html',result
    
    except Exception as e:
        print str(e)
        LOG.exception("查询异常，异常为：%s" % e)
        return u'系统异常，更新失败',500
    
def export(request):
    '''导出销售记录到excel'''
    response = HttpResponse(mimetype="application/ms-excel")
    response['Content-Disposition'] = 'attachment; filename=saleinfo_report.xls'
    wb = xlwt.Workbook()
    sheet_name = 'saleinfo'
    ws = wb.add_sheet(sheet_name)
    
    from xlwt import Font,Borders,Alignment,XFStyle
    fnt = Font()
    fnt.name = 'Arial'
    fnt.colour_index = 0
    fnt.bold = True
    
    borders = Borders()
    borders.left = 1
    borders.right = 1
    borders.top = 1
    borders.bottom = 1
    
    al = Alignment()
    al.horz = Alignment.HORZ_CENTER
    al.vert = Alignment.VERT_CENTER
    style = XFStyle()
    style.font = fnt
    style.alignment = al
    style2 = XFStyle()
    style2.alignment = al

    trades = Trade.objects.filter(deleted=False, tradeType=Constant.TRADE_BUY_LOTTERY).order_by('-tradeTime')
    ws.write(0, 0, 'No.',style)
    ws.write(0, 1, u'帐号',style)
    ws.write(0, 2, u'用户名',style)
    ws.write(0, 3, u'彩票类型',style)
    ws.write(0, 4, u'期次',style)
    ws.write(0, 5, u'交易金额',style)
    ws.write(0, 6, u'交易方法',style)
    ws.write(0, 7, u'交易时间',style)
    ws.write(0, 8, u'交易状态',style)
    
    rownum = 1
    for trade in trades:
        if trade.tradeTicketType is None:
            trade.tradeTicketType="暂没种类"
        elif trade.tradeTicketType or not trade.tradeTicketType:
            trade.tradeTicketType=type_dict3.get(trade.tradeTicketType)
        
        ws.write(rownum,0,rownum,style2)
        ws.write(rownum,1,trade.accountid.account,style2)
        ws.write(rownum,2,trade.accountid.name,style2)
        ws.write(rownum,3,trade.tradeTicketType,style2)
        ws.write(rownum,4,trade.period.periodid,style2)
        ws.write(rownum,5,trade.tradeAmount,style2)
        ws.write(rownum,6,method_dict.get(trade.tradeMethod),style2)
        ws.write(rownum,7,utils.convert_datetime_to_str(trade.tradeTime, "%Y-%m-%d %H:%M:%S"),style2)
        ws.write(rownum,8,type_dict1.get(trade.status),style2)
        rownum += 1
                
    wb.save(response)
    return response

def statitsticsReport(request,start,end):
    '''导出月度统计报表'''
    try:
        response = HttpResponse(mimetype="application/ms-excel")
        response['Content-Disposition'] = 'attachment; filename=tongji_report.xls'
        wb = xlwt.Workbook()
        sheet_name = 'tongji'
        ws = wb.add_sheet(sheet_name)
        
        from xlwt import Font,Borders,Alignment,XFStyle
        fnt = Font()
        fnt.name = 'Arial'
        fnt.colour_index = 0
        fnt.bold = True
        
        borders = Borders()
        borders.left = 1
        borders.right = 1
        borders.top = 1
        borders.bottom = 1
        
        al = Alignment()
        al.horz = Alignment.HORZ_CENTER
        al.vert = Alignment.VERT_CENTER
        style = XFStyle()
        style.font = fnt
        style.alignment = al
        style2 = XFStyle()
        style2.alignment = al
    
        ws.write(0, 0, 'No.',style)
        ws.write(0, 1, u'帐号',style)
        ws.write(0, 2, u'余额',style)
        ws.write(0, 3, u'销售总额',style)
        ws.write(0, 4, u'刷卡销售额',style)
        ws.write(0, 5, u'投币销售额',style)
        ws.write(0, 6, u'充值金额',style)
        ws.write(0, 7, u'刷卡充值',style)
        ws.write(0, 8, u'人工充值',style)
        ws.write(0, 9, u'提成金额',style)
        ws.write(0, 10, u'返奖金额',style)
        ws.write(0, 11, u'提现金额',style)
        
        rownum = 1
        accounts = Account.objects.filter(deleted=False)
        start = start + " 00:00:00"
        end = end + " 23:59:59"
        for account in accounts:
            if account.accountType==Constant.TIANFU_ACCOUNT:
                continue
            data = total_inout(account,start,end)
            ws.write(rownum,0,rownum,style2)
            ws.write(rownum,1,account.account,style2)
            ws.write(rownum,2,account.balance,style2)
            ws.write(rownum,3,data['xiaoshou_total'],style2)
            ws.write(rownum,4,data['xs_bank'],style2)
            ws.write(rownum,5,data['xiaoshou'],style2)
            ws.write(rownum,6,data['chongzhi_total'],style2)
            ws.write(rownum,7,data['cz_bank'],style2)
            ws.write(rownum,8,data['cz_rg'],style2)
            ws.write(rownum,9,data['ticheng_total'],style2)
            ws.write(rownum,10,data['prize_total'],style2)
            ws.write(rownum,11,data['tixian_total'],style2)
            rownum += 1
                    
        ws.write(rownum,0,u"统计时间段：%s~%s"%(start,end))
        wb.save(response)
        return response
    except Exception as e:
        LOG.exception("导出报表失败，异常信息为：%s" % e)

